package org.pushforward.iwillsurvive.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
import org.pushforward.iwillsurvive.helpers.Utils;

/**
 *	An Database Manager class<br/>
 *	<br/>
 *	Make sure all strings fed to methods contain only one statement
 * @author Sabre Runner
 */
public class DatabaseManager
{
	/**
     * ManagerHolder is loaded on the first execution of DatabaseManager.getInstance() 
     * or the first access to ManagerHolder.INSTANCE, not before.
     */
    private static class ManagerHolder
    { 
    	public static final DatabaseManager INSTANCE = new DatabaseManager();
    }
    public static DatabaseManager getInstance()
    {
    	return ManagerHolder.INSTANCE;
    }
	
	private PoolProperties p = new PoolProperties();
	public static DataSource connectionPool = new DataSource();
	public static String appbase = null;
	private int nextID = -1; 

	private DatabaseManager()
	{
		//p.setUrl("jdbc:mysql://localhost:3306/iwillsurvivedb");
		p.setDriverClassName("com.mysql.jdbc.Driver");
		//p.setUsername("srunner"); 
		//p.setPassword("srunner");
		p.setJmxEnabled(true);
		p.setTestWhileIdle(false);
		p.setTestOnBorrow(true);
		p.setValidationQuery("select 1");
		p.setTestOnReturn(false);
		p.setValidationInterval(30000);
		p.setTimeBetweenEvictionRunsMillis(30000);
		p.setMaxActive(100);
		p.setInitialSize(10);
		p.setMaxWait(10000);
		p.setRemoveAbandonedTimeout(60);
		p.setMinEvictableIdleTimeMillis(30000);
		p.setMinIdle(10);
		p.setLogAbandoned(true);
		p.setRemoveAbandoned(true);
		p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;" +
							  "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
		
		connectionPool.setPoolProperties(p);
	}

	/**
	 * checks if the given statement is a manipulation statement or a query
	 * 
	 * @param statement the statement to check
	 * @return true if a manipulation statement, false otherwise
	 */
	private boolean isManipulationStatement(String statement)
	{
		if (statement.contains("create") ||
				statement.contains("update") ||
				statement.contains("insert") ||
				statement.contains("delete") ||
				statement.contains("truncate"))
		{
			Utils.Log("Statement is manipulation.");
			return true;
		} 
		else
		{
			Utils.Log("Statement is query.");
			return false;
		}
	}

	private boolean patchParameter(PreparedStatement statement, 
										  int index, 
										  TypeValuePair parameter) 
										  throws NumberFormatException, SQLException
	{
		switch (parameter.Type())
		{
			case INT:
				statement.setInt(index, parameter.IntValue());
				return true;
			case LONG:
				statement.setLong(index, parameter.LongValue());
				return true;
			case FLOAT:
				statement.setFloat(index, parameter.FloatValue());
				return true;
			case DOUBLE:
				statement.setDouble(index, parameter.DoubleValue());
				return true;
			case STRING:
				statement.setString(index, parameter.StringValue());
				return true;
			case TIMESTAMP:
				statement.setTimestamp(index, parameter.TimestampValue());
				return true;
			case BOOLEAN:
				statement.setBoolean(index, parameter.BooleanValue());
				return true;
			default:
				return false;
		}
	}
	
	/**
	 * Patches the statement with given parameters.</br>
	 * Designed for security.
	 * @param statement
	 * @param parameters the parameters in a TypeValuePair array. Send null for no parameters
	 * @return
	 * @throws NumberFormatException
	 * @throws SQLException
	 */
	private boolean patchStatement(PreparedStatement statement, 
								   TypeValuePair[] parameters,
								   int count) 
								   throws NumberFormatException, SQLException
	{
		boolean result = true;
		
		if (parameters == null)
		{
			return result;
		}
		
		for (int index = 0; index < count; index++)
		{
			result = result && patchParameter(statement, index+1, parameters[index]);
		}
		
		return result;
	}

	private boolean closeStatementAndConnection(PreparedStatement preparedStatement, Connection con)
	{
		if  (null != preparedStatement)
		{
			try
			{
				preparedStatement.close();
			} catch (SQLException e)
			{
				System.err.println(e.getMessage());
				e.printStackTrace();
				return false;
			}
		}
		if (null != con)
		{
			try
			{
				con.close();
			} catch (SQLException e)
			{
				System.err.println(e.getMessage());
				e.printStackTrace();
				return false;
			}
		}
		
		return true;
	}
	
	/**
	 * Attempts a manipulation statement on the database
	 * 
	 * @param statement the statement to attempt
	 * @return true if it went through, false otherwise
	 * @throws SQLException if the statement completely failed
	 */
	private boolean manipulateDatabase(String statement, TypeValuePair[] parameters) throws SQLException
	{
		// basic setup
		Connection con = null;
		PreparedStatement preparedStatement = null;
		boolean result = false;
		SQLException exception = null;
		
		try
		{
			// try to execute update
			con = connectionPool.getConnection();
			con.setAutoCommit(false);
			preparedStatement = con.prepareStatement(statement);
			patchStatement(preparedStatement, parameters, NumOfParametersIn(statement));
			if (0 == preparedStatement.executeUpdate())
			{
				// false if update did nothing
				result = false;
			} else
			{
				// true if something updated
				result = true;
			}
			con.commit();
			Utils.Log("[" + preparedStatement.toString() + "] Commited.");
		}
		catch (SQLException e)
		{
			// catch the exception and keep it. Need to close things first.
			exception = e;
			if (con != null)
			{
				con.rollback();
				Utils.Log("[" + preparedStatement.toString() + "] Rolled back.");
			}
		}
		catch (Exception e)
		{
			System.err.println(e.getMessage());
			e.printStackTrace();
		}
		finally
		{
			if (!closeStatementAndConnection(preparedStatement, con) && exception == null)
			{
				return false;
			}
		}
		// if there was an exception, throw it upwards.
		if (null != exception)
		{
			throw exception;
		}
		return result;
	}

	/**
	 * Attempts a query statement on the database
	 * 
	 * @param statement the statement to attempt
	 * @return the result set gathered
	 * @throws SQLException if the statement completely failed
	 */
	private String queryDatabase(String statement, TypeValuePair[] parameters) throws SQLException
	{
		// basic setup
		Connection con = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		SQLException exception = null;
		String resultString = null;

		try
		{
			// get a connection
			con = connectionPool.getConnection();
			con.setAutoCommit(false);
			// set up a statement
			preparedStatement = con.prepareStatement(statement);
			patchStatement(preparedStatement, parameters, NumOfParametersIn(statement));
			// execute and get a result set
			resultSet = preparedStatement.executeQuery();
			con.commit();
			Utils.Log("[" + preparedStatement.toString() + "] Commited.");
			if (/*resultSet.next() &&*/ !resultSet.isAfterLast())
			{
				resultString = toString(resultSet);
				Utils.Log("This is the result: \n\t\t\t\t" + resultString);
			}
		} 
		catch (SQLException e)
		{
			// catch the exception and keep it. Need to close things first.
			exception = e;
			if (con != null)
			{
				con.rollback();
			}
		} finally
		{
			if (null != resultSet)
			{
				resultSet.close();
			}
			closeStatementAndConnection(preparedStatement, con);
		}
		// if there was an exception, throw it upwards.
		if (null != exception)
		{
			throw exception;
		}
		return resultString;
	}

	/**
	 * Executes the statements one by one until one succeeds.</br>
	 * If there is a result set, it is outputed through the string.</br>
	 * All parameters must not be null
	 * @param statements the statements to attempt
	 * @param parameters the list of parameters for each statement
	 * @param resultSetOutput the result set of the executed statement if there is one
	 * @return true if successful, false otherwise. If false, state of <b>resultSetOutput</b> undefined
	 */
	public boolean ExecuteStatements(String[] statements, 
											TypeValuePair[][] parameters,
											StringBuilder resultSetOutput)
	{
		if (statements == null || parameters == null || resultSetOutput == null)
		{
			return false;
		}
		// basic setup
		boolean result = false;
		String resultSet = null;

		for (int index = 0; index < statements.length; index++)
		{
			try
			{
				if (isManipulationStatement(statements[index]))
				{
					result = manipulateDatabase(statements[index], parameters[index]);
					if (statements[index].indexOf("posts") != -1)
					{
						nextID = -1;
					}
					break;
				}
				else
				{
					if ((resultSet = queryDatabase(statements[index], parameters[index])) != null &&
						 !resultSet.isEmpty())
					{
						resultSetOutput.setLength(0);
						resultSetOutput.append(resultSet);
						result = true;
						break;
					}
				}
			} 
			catch (SQLException e)
			{
				Utils.Log("Exception during DatabaseManager.ExecuteStatements: " + e.getMessage());
				e.printStackTrace();
				result = false;
			}
		}
		return result;
	}

	public boolean ExecuteStatements(String[] statements, TypeValuePair[][] parameters)
	{
		StringBuilder result = new StringBuilder();
		return ExecuteStatements(statements, parameters, result);
	}

	/**
	 * Returns the result set as a string with comma seperated columns and EOL seperated rows
	 * @param resultSet
	 * @return
	 */
	private String toString(ResultSet resultSet)
	{
		StringBuilder resultBuilder = new StringBuilder();

		try
		{
			if ((resultSet == null) || (resultSet.isClosed()))
			{
				return null;
			}
			
			int columnCount = resultSet.getMetaData().getColumnCount();
			
			while (resultSet.next())
			{
				for (int index = 1; index < columnCount + 1; index++)
				{
					resultBuilder.append(resultSet.getString(index));
					if (index < columnCount) resultBuilder.append(",");
				}
				resultBuilder.append("\n");
			}
			return resultBuilder.toString();
		} catch (SQLException e)
		{
			return null;
		}
	}

	public TypeValuePair[][] GetEmptyParameters(int numOfStatements)
    {
		Utils.Log("Getting " + Integer.toString(numOfStatements) + " Empty Parameters.");
		TypeValuePair[][] emptyParameters = new TypeValuePair[numOfStatements][1];
            
        for (int index = 0; index < numOfStatements; index++)
        {
        	emptyParameters[index] = null;
        }
            
        return emptyParameters;
    }

	private int NumOfParametersIn(String statement)
	{
		if (statement.indexOf("?") == -1)
		{
			return 0;
		}
		return statement.split("\\?").length-1;
	}

	public int NextID()
	{
		if (nextID == -1)
		{
			UpdateNextID();
		}
		return nextID;
	}	
	public void UpdateNextID()
	{
		String[] updatePosts = {"select * from posts;"};
		StringBuilder resultSet = new StringBuilder();
		
		ExecuteStatements(updatePosts, GetEmptyParameters(1), resultSet);
		
		String resultString = resultSet.toString();
		
		if (resultString.indexOf("\n") == -1)
		{
			nextID = 0;
		}
		else
		{
			String[] results = resultString.split("\n");
			String[] lastFields = results[results.length-1].split(",");
			nextID = Integer.parseInt(lastFields[0]) + 1;
		}

		Utils.Log("NextID updated: " + nextID);
	}


	public void SetDBName(String dbName)
	{
		connectionPool.setUrl("jdbc:mysql://localhost:3306/" + dbName);
	}
	public void SetUsername(String dbUsername)
	{
		connectionPool.setUsername(dbUsername);
	}
	public void SetPassword(String dbPassword)
	{
		connectionPool.setPassword(dbPassword);
	}
	
}
